﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;
using System.Data.Objects;

namespace DAL
{
    public class NhanKhauDAL
    {
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHK(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK],[NgayNhap],ISNULL([NgayXoa],'') as NgayXoa,ISNULL([LyDoXoa],0) as LyDoXoa from NhanKhau where MaSHK=" + MaSHK;
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        //public static List<NhanKhauPublic> LoadNhanKhau()
        //{ }
        public static void InsertNhanKhau(NhanKhauPublic _nk)
        {
            Nhankhau nk = new Nhankhau();
            nk.HoTen = _nk.HoTen;
            nk.GioiTinh = _nk.GioiTinh;
            nk.NgaySinh = _nk.NgaySinh;
            nk.NoiSinh = _nk.NoiSinh;
            // nk.QuanHe = _nk.MaQH;
            nk.MaQH = _nk.MaQH;
            nk.MaQT = _nk.MaQT;
            nk.MaDT = _nk.MaDT;
            nk.QueQuan = _nk.QueQuan;
            nk.MaSHK = _nk.MaSHK;
            nk.MaKS = null;
            nk.MaNK = _nk.MaNK;
            nk.MaKT = null;
            nk.MaTG = _nk.MaTG;
            nk.MaSHK = nk.MaSHK;
            nk.MaTD = _nk.MaTD;
            nk.CMND = _nk.CMND;
            nk.TenGoiKhac = _nk.TenGoiKhac;
            nk.NgheNghiep = _nk.NgheNghiep;
            Common.db.AddToNhankhaus(nk);
            Common.db.SaveChanges();
        }
        public static void InsertNhanKhauKS(NhanKhauPublic _nk)
        {
            Nhankhau nk = new Nhankhau();
            nk.HoTen = _nk.HoTen;
            nk.GioiTinh = _nk.GioiTinh;
            nk.NgaySinh = _nk.NgaySinh;
            nk.NoiSinh = _nk.NoiSinh;
            // nk.QuanHe = _nk.MaQH;
            nk.MaQH = _nk.MaQH;
            nk.MaQT = _nk.MaQT;
            nk.MaDT = _nk.MaDT;
            nk.QueQuan = _nk.QueQuan;
            nk.MaSHK = _nk.MaSHK;
            nk.MaKS = _nk.MaKS;
            nk.MaNK = _nk.MaNK;
            nk.MaKT = null;
            nk.MaTG = _nk.MaTG;
            nk.MaSHK = nk.MaSHK;
            nk.MaTD = _nk.MaTD;
            nk.CMND = null;
            nk.TenGoiKhac = _nk.TenGoiKhac;
            nk.NgheNghiep = _nk.NgheNghiep;
            Common.db.AddToNhankhaus(nk);
            Common.db.SaveChanges();
        }
        public static int LoadMaNK()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaNK),0) as MaNK from NhanKhau");
            return nk.FirstOrDefault();
        }
        public static void UpdateKhaiTu(int MaKT, int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaKT=" + MaKT + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static void UpdateMaSHK(int MaSHK, int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaSHK=" + MaSHK + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static int CountNhanKhau()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null");
            return nk.ToList()[0];
        }
        public static int CountNhanKhauNam()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null and GioiTinh=1");
            return nk.ToList()[0];
        }
        public static int CountNhanKhauNu()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null and GioiTinh=0");
            return nk.ToList()[0];
        }
        public static NhanKhauPublic LoadNhanKhauByMaNK(int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaNK=" + MaNK);
            return nk.FirstOrDefault();
        }
        public static NhanKhauPublic LoadNhanKhauByCMND(string CMND)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where CMND=" + "'" + CMND + "'");
            return nk.FirstOrDefault();
        }
        public static void UpdateQuanHe(int MaNK, byte MaQH)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaQH=" + MaQH + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHKCha(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen] from NhanKhau where MaSHK=" + MaSHK + "and GioiTinh=1";
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHKMe(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen] from NhanKhau where MaSHK=" + MaSHK + "and GioiTinh=0";
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        public static int CoutSoHoKhauByYear(int firstyear, int lastyear)
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"selct count(MaSHK) from NhanKhau where year(NgayCap)>=" + firstyear + "and year(NgayCap)<=" + lastyear);
            return shk.FirstOrDefault();
        }
        public static NhanKhauPublic LoadNhanKhauByMaKS(int MaKS)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaKS=" + MaKS);
            return nk.FirstOrDefault();
        }
        public static void UpdateMaKS(int MaKS, int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaKS=" + MaKS + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static List<NhanKhauPublic> LoadNhanKhauKS()
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaKS is not null");
            return nk.ToList();
        }
        public static List<NhanKhauPublic> LoadListNhanKhauByMaKS(int MaKS)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaKS=" + MaKS);
            return nk.ToList();
        }
        public static List<NhanKhauPublic> LoadNhanKhauKT()
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaKT is not null");
            return nk.ToList();
        }
        public static List<NhanKhauPublic> LoadListNhanKhauByMaKT(int MaKT)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("select SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaKT=" + MaKT);
            return nk.ToList();
        }
        public static void UpdateNhanKhauKS(string HoTen, DateTime NgaySinh, int NoiSinh, byte DanToc, byte QuocTich, bool GioiTinh, string QueQuan, int MaKS)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("Update NhanKhau set HoTen=N'" + HoTen + "',NgaySinh='" + NgaySinh + "',NoiSinh=" + NoiSinh + ",MaDT=" + DanToc + ",MaQT=" + QuocTich + ",GioiTinh=" + GioiTinh + ",QueQuan=N'" + QueQuan + "' where MaKS=" + MaKS);
            Common.db.SaveChanges();
        }
        public static void DeleteNhanKhau(int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("delete from NhanKhau where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static void UpdateNhanKhau(string HoTen, bool GioiTinh, int NoiSinh, DateTime NgaySinh, byte DanToc, byte QuocTich, byte TonGiao, byte TrinhDo, string NgheNghiep, string QueQuan, int MaQH, string CMND, string TenGoiKhac, int MaNK)
        {
            string chuoi = "update NhanKhau set HoTen=N'" + HoTen + "',GioiTinh='" + GioiTinh + "',NoiSinh=" + NoiSinh + ",NgaySinh='" + NgaySinh + "',MaDT=" + DanToc + ",MaQT=" + QuocTich + ",MaTG=" + TonGiao + ",MaTD=" + TrinhDo + ",NgheNghiep=N'" + NgheNghiep + "',QueQuan=N'" + QueQuan + "',MaQH=" + MaQH + ",CMND='" + CMND + "',TenGoiKhac=N'" + TenGoiKhac + "' where MaNK=" + MaNK;
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static List<NhanKhauPublic> NVQS()
        {
            string chuoi = @"select HoTen, NgaySinh, GioiTinh, DiaChi,TenPhuongXa
                            from 
                            (
                            Select HoTen,NgaySinh,GioiTinh,cast(SoNha as varchar(8))+', '+Duong+', '+cast(KhuPho as varchar(2)) as DiaChi,
                            LEFT(RIGHT('0'+CAST(MaNK as nvarchar(8)),8),2) as c1

                            from NhanKhau,SoHoKhau 
                            where NhanKhau.MaSHK = SoHoKhau.MaSHK and GioiTinh=1 and NhanKhau.NgayXoa is null and
                            DATEDIFF(yyyy,NgaySinh,GETDATE()) between 18 and 25
                            ) 
                            as t1,
                            (
                            select right(ThietLap.MaPX,2) as c2,TenPhuongXa from PhuongXa, ThietLap where ThietLap.MaPX=PhuongXa.MaPX)
                             as t2
                            where t1.c1=t2.c2";
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nk.ToList();
        }
        public static List<NhanKhauPublic> TimKiemTen(string HoTen)
        {
            string chuoi = "SELECT [MaNK],[HoTen],[TenGoiKhac],ISNULL([CMND],0) as CMND,ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK],[NgayNhap],ISNULL([NgayXoa],'') as NgayXoa,ISNULL([LyDoXoa],0) as LyDoXoa from NhanKhau where HoTen like N'" + HoTen+"'";
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nk.ToList();
        }
        public static List<NhanKhauPublic> TimKiemNS(DateTime NgaySinh)
        {
            string chuoi = "SELECT [MaNK],[HoTen],[TenGoiKhac],ISNULL([CMND],0) as CMND,ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK],[NgayNhap],ISNULL([NgayXoa],'') as NgayXoa,ISNULL([LyDoXoa],0) as LyDoXoa from NhanKhau where NgaySinh='" + NgaySinh + "'";
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nk.ToList();
        }
    }
}
